﻿using System;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using DBAccessLibrary;
using DevExpress.Data.Linq;
using DevExpress.Xpf.Editors.Settings;
using QueryResource.Common;
using Skynet.CommonLibrary.Extension;
using Microsoft.Win32;
using System.Text;
using System.Collections.Generic;
using System.Windows;
using System.Threading.Tasks;
using SQLQuery.Common;
using System.Windows.Media;

namespace SQLQuery.UI
{
    /// <summary>
    /// GridViewControl.xaml 的交互逻辑
    /// </summary>
    [System.ComponentModel.DesignTimeVisible(false)]
    public partial class GridViewControl
    {
        private string _sql = string.Empty;
        private DataSet _ds;
        private const bool IsLinqBinding = false;
        public GridViewControl()
        {
            InitializeComponent();
        }

        #region 绑定1

        /// <summary>
        /// 绑定数据
        /// </summary>
        private void DataBind()
        {
            _ds = null;
            //this.gridControl1.Dispatcher.BeginInvoke(new Action(() =>
            //   {
            this.gridControl1.Columns.Clear();
            this.gridControl1.ItemsSource = null;
            //}));
            Stopwatch sw = Stopwatch.StartNew();
            Action sqlAction = () =>
            {
                //首先判断 行数。如果超过 10万行 则提醒
                try
                {
                    string sql = "select count(0) from (" + _sql + ")";
                    object obj = DBOperatorFactory.GetOperator.ExecScalar(sql);
                    if (obj != null)
                    {
                        decimal count = Convert.ToDecimal(obj.ToString());
                        if (count > 100000)
                        {
                            bool isReturn = false;
                            this.InvokeEx(() =>
                            {
                                if (MessageBoxHelper.Show("当前查询结果为" + count + "，可能会影响查询速度，是否继续进行?", "提醒", MessageBoxButton.OKCancel, MessageBoxImage.Question) == MessageBoxResult.Cancel)
                                {
                                    isReturn = true;

                                    IsBusy = false;
                                    lbTime.Content = "执行时间:0";
                                    lbCount.Content = "查询行数:0";
                                    this.busyCtrl.IsBusy = false;
                                }
                            });
                            if (isReturn)
                            {
                                return;
                            }
                        }
                    }
                }
                catch { }
                try
                {
                    _ds = DBOperatorFactory.GetOperator.ExecSqlToDS(_sql);
                }
                catch (Exception ex)
                {
                    this.gridControl1.InvokeEx(() =>
                    {
                        IsBusy = false;
                        lbTime.Content = "执行时间:0";
                        lbCount.Content = "查询行数:0";
                        this.busyCtrl.IsBusy = false;
                    });
                    WriteLog(ex.Message, Colors.Red);
                }
            };
            Task.Factory.FromAsync(sqlAction.BeginInvoke, sqlAction.EndInvoke, null).ContinueWith(
                t =>
                {
                    sw.Stop();
                    if (t.Exception != null)
                    {
                    }
                    else
                    {
                        if (_ds != null && _ds.Tables.Count > 0)
                        {
                            this.gridControl1.InvokeEx(() =>
                            {
                                lbTime.Content = "执行时间:" + sw.Elapsed.ToString();
                                if (_ds != null && _ds.Tables.Count > 0)
                                {
                                    this.lbCount.Content = "查询行数:" + _ds.Tables[0].Rows.Count;
                                    foreach (DataColumn column in _ds.Tables[0].Columns)
                                    {
                                        DevExpress.Xpf.Grid.GridColumn gc = new DevExpress.Xpf.Grid.GridColumn
                                                                                {
                                                                                    FieldName = column.ColumnName,
                                                                                    ReadOnly =true
                                                                                };

                                        if (column.DataType == typeof(DateTime))
                                        {
                                            gc.EditSettings = new TextEditSettings
                                                                  {
                                                                      DisplayFormat = "yyyy-MM-dd HH:mm:ss.ffffff"
                                                                  };
                                        }
                                        if (column.DataType.IsGenericType)
                                        {
                                            gc.EditSettings = new TextEditSettings();
                                        }
                                        this.gridControl1.Columns.Add(gc);
                                    }
                                    this.gridControl1.ItemsSource = _ds.Tables[0];
                                }
                                else
                                {
                                    this.lbCount.Content = "查询行数:0";
                                    this.gridControl1.ItemsSource = null;
                                }
                                //通知 数据已经加载成功
                                WriteDataSucess();
                                WriteLog(string.Format("执行成功.{0}-----耗时:{1}{0}-----行数:{2}{0}{3}{0}",
                                    Environment.NewLine, 
                                    sw.Elapsed.ToString(), 
                                    _ds.Tables[0].Rows.Count,
                                    "*****************************华丽的分割线*******************************")
                                    , Colors.Black);
                                IsBusy = false;
                                this.busyCtrl.IsBusy = false;
                            });
                        }
                        else
                        {
                            if (IsBusy)
                            {
                                this.gridControl1.InvokeEx(() =>
                                {
                                    IsBusy = false;
                                    this.busyCtrl.IsBusy = false;
                                    WriteLog("命令执行成功."+Environment.NewLine, Colors.Blue);
                                });
                            }
                        }
                    }
                });
        }
        #endregion

        #region 绑定2
        public void DataBind(string sql)
        {
            IsBusy = true;
            _sql = sql;
            WriteLog(_sql, Colors.Black);
            if (IsLinqBinding)
            {
                this.gridControl1.BeginInvokeEx(new Action(() =>
                {
                    LinqInstantFeedbackSource source = new LinqInstantFeedbackSource();
                    source.KeyExpression = "Dynamic_ID";
                    source.DefaultSorting = "Dynamic_ID";
                    source.GetQueryable += new EventHandler<GetQueryableEventArgs>(SourceGetQueryable);
                    this.gridControl1.View.RowAnimationKind = DevExpress.Xpf.Grid.RowAnimationKind.None;
                    gridControl1.ItemsSource = source;
                }));
            }
            else
            {
                this.busyCtrl.IsBusy = true;
                this.busyCtrl.Text = "正在加载数据，请稍等...";
               
                //ThreadPool.QueueUserWorkItem(new WaitCallback(obj => {
                    DataBind();
                //}));
            }
        }

        void SourceGetQueryable(object sender, GetQueryableEventArgs e)
        {
            IList list = null;
            try
            {
                Stopwatch sw = Stopwatch.StartNew();
                DataSet ds = DBOperatorFactory.GetOperator.ExecSqlToDS(_sql);
                _ds = ds;
                

                if (ds.Tables.Count == 0)
                {
                    WriteLog("命令执行成功."+Environment.NewLine, Colors.Blue);
                    sw.Stop();
                }
                else
                {
                    list = DynamicInterfaceHelper.ConvertAnonymousDataSetToEntity(ds);
                    sw.Stop();
                    this.gridControl1.InvokeEx(() =>
                    {
                        try
                        {
                            this.gridControl1.Columns.Clear();
                            lbTime.Content = "执行时间:" + sw.Elapsed.ToString();
                            if (list.Count > 0)
                            {
                                foreach (PropertyInfo info in list[0].GetType().GetProperties())
                                {
                                    if (info.Name == "Dynamic_ID")
                                        continue;
                                    DevExpress.Xpf.Grid.GridColumn gc = new DevExpress.Xpf.Grid.GridColumn
                                                                            {
                                                                                FieldName = info.Name
                                                                            };

                                    if (info.PropertyType == typeof(DateTime) || info.PropertyType.IsGenericType)
                                    {
                                        gc.EditSettings = new TextEditSettings
                                                              {
                                                                  DisplayFormat = "yyyy-MM-dd HH:mm:ss"
                                                              };
                                    }
                                   
                                    this.gridControl1.Columns.Add(gc);
                                }
                            }
                            else
                            {
                                foreach (DataColumn colum in ds.Tables[0].Columns)
                                {
                                    if (colum.ColumnName == "Dynamic_ID")
                                        continue;
                                    DevExpress.Xpf.Grid.GridColumn gc = new DevExpress.Xpf.Grid.GridColumn
                                                                            {
                                                                                FieldName = colum.ColumnName
                                                                            };
                                    this.gridControl1.Columns.Add(gc);
                                }
                            }

                            if (list != null && list.Count > 0)
                            {
                                this.lbCount.Content = "查询行数:" + ds.Tables[0].Rows.Count;
                            }
                            else
                            {
                                this.lbCount.Content = "查询行数:0";
                            }
                            //通知 数据已经加载成功
                            WriteDataSucess();
                        }
                        catch (Exception ex)
                        {
                            WriteLog(ex.Message, Colors.Red);
                        }
                        finally
                        {
                            ((LinqInstantFeedbackSource) sender).GetQueryable -= SourceGetQueryable;
                        }
                    });
                }
                e.QueryableSource = list != null ? list.AsQueryable() : null;

                IsBusy = false;
            }
            catch (Exception ex)
            {
                IsBusy = false;
                ((LinqInstantFeedbackSource) sender).GetQueryable -= SourceGetQueryable;
                e.QueryableSource = null;
                this.gridControl1.InvokeEx(() =>
               {
                   lbTime.Content = "执行时间:0";
                   lbCount.Content = "查询行数:0";
               });
                WriteLog(ex.Message, Colors.Red);
            }
        }
        #endregion


        public bool IsBusy { get; set; }
        #region 外界通信
        public Action<string, Color> AppendLogAction
        {
            private get;
            set;
        }
        public Action<GridViewControl> AppendDataSucess { get; set; }
        public Action<string,string,bool> AppendSqlContent { get; set; }

        private void WriteLog(string msg, Color errorColor)
        {
            if (AppendLogAction != null)
            {
                AppendLogAction(msg, errorColor);
            }
        }
        /// <summary>
        /// 数据加载成功后 用于通知界面激活当前窗口
        /// </summary>
        private void WriteDataSucess()
        {
            if (AppendDataSucess != null)
            {
                AppendDataSucess(this);
            }
        }
        private void WriteSqlContent(string tableName,string content)
        {
            if (AppendSqlContent != null)
            {
                AppendSqlContent(tableName,content,false);
            }
        }
        #endregion

        /// <summary>   
        /// 常用方法，列之间加\t开。   
        /// </summary>   
        /// <remarks>   
        /// using System.IO;   
        /// </remarks>
        private void DataSetToExcel(DataSet ds)
        {
            SaveFileDialog dlg = new SaveFileDialog
                                     {
                                         Filter = "Execl files (*.xls)|*.xls",
                                         CheckFileExists = false,
                                         CheckPathExists = false,
                                         FilterIndex = 0,
                                         RestoreDirectory = true,
                                         CreatePrompt = true,
                                         Title = "保存为Excel文件"
                                     };

            if (dlg.ShowDialog().Value)
            {
                ToExcel.ExportToExcel(ds, dlg.FileName);
            }

        }

        private void BtnExceptItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
        {
            if (_ds == null || _ds.Tables.Count == 0)
                return;
            try
            {
                DataSetToExcel(_ds);
            }
            catch (Exception ex)
            {
                MessageBoxHelper.Show(ex.Message);
            }
        }

        private void BtnExceptSqlItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
        {
            if (_ds == null || _ds.Tables.Count == 0)
                return;
            if (_sql != string.Empty)
            {
                //获取表名
                string tableName = string.Empty;
                _sql = _sql.Replace(Environment.NewLine, "");
                string[] content = _sql.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                for(int i=0;i<content.Length;i++)
                {
                    if (content[i].ToLower() == "from")
                    {
                        tableName = content[i + 1].Trim();
                        break;
                    }
                }

                //判断是否有时间字段。因为DB2跟ORACLE的处理方式不同
                string dbType = string.Empty;
                foreach (DataColumn c in _ds.Tables[0].Columns)
                {
                    if (c.DataType == typeof(DateTime))
                    {
                        MessageBoxResult result = MessageBoxHelper.Show("由于字段包含时间类型，请选择导出的格式。\r\n（Y：DB2 N：ORACLE）","提示", MessageBoxButton.YesNoCancel, MessageBoxImage.Information);
                        if (result == MessageBoxResult.Yes)
                            dbType = "DB2";
                        else if (result == MessageBoxResult.No)
                            dbType = "ORACLE";
                        break;
                    }
                }
              
                StringBuilder sb = new StringBuilder();
               
                foreach (DataRow row in _ds.Tables[0].Rows)
                {
                    List<string> columList = new List<string>();
                    List<string> valueList = new List<string>();
                    foreach (DataColumn column in _ds.Tables[0].Columns)
                    {
                        if (column.ColumnName == "Dynamic_ID")
                            continue;
                        columList.Add(column.ColumnName);
                        if (column.DataType == typeof(string))
                        {
                            if (row[column.ColumnName] == null)
                            {
                                valueList.Add("''");
                            }
                            else
                            {
                                valueList.Add("'" + row[column.ColumnName] + "'");
                            }
                        }
                        else if(column.DataType == typeof(DateTime))
                        {
                            if (row[column.ColumnName] == null || string.IsNullOrEmpty(row[column.ColumnName].ToString()))
                            {
                                valueList.Add("Null");
                            }
                            else
                            {
                                DateTime dt = Convert.ToDateTime(row[column.ColumnName].ToString());
                                if (dbType == "DB2")
                                {
                                    valueList.Add(string.Format("timestamp('{0}')",
                                                                dt.ToString("yyyy-MM-dd-HH.mm.ss")));
                                }
                                else if (dbType == "ORACLE")
                                {
                                    valueList.Add(string.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss')",
                                                                dt.ToString("yyyy-MM-dd HH:mm:ss")));
                                }
                                else
                                {
                                    valueList.Add("'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                                }
                            }
                        }
                        
                        else
                        {
                            if (row[column.ColumnName] == null || row[column.ColumnName].ToString() == string.Empty)
                            { valueList.Add("0"); }
                            else
                            {
                                valueList.Add(row[column.ColumnName].ToString());
                            }
                        }
                    }
                    sb.AppendLine(string.Format("insert into {0}({1}) values({2});",
                    tableName,
                    string.Join(",", columList.ToArray()),
                    string.Join(",", valueList.ToArray())
                    ));
                }

                WriteSqlContent(tableName,sb.ToString());
                
            }
        }
    }
}
